package com.novoda.runbuddy.provider;

import java.util.Calendar;

import com.novoda.runbuddy.util.DBHelperTools;

public class RunDBSQL {
	public static final int		DATABASE_VERSION							= 1;
	public static final String	DATABASE_NAME								= "runningBuddyDB2";

	public static final String	RUNNING_ENTRIES_TABLE						= "run";
	public static final String	RUNSEGMENT_ENTRIES_TABLE					= "runSegment";

	public static final String	TIME_TABLE									= "time";

	public static final String	RUN_ID										= "_id";
	public static final String	RUN_ROUTE_ID								= "_idRoute";
	public static final String	RUN_NAME									= "name";
	public static final String	RUN_TOTAL_TIME								= "totalTime";
	public static final String	RUN_MODIFIED_AT								= "modifiedAt";
	public static final String	RUN_CREATED_AT								= "createdAt";

	public static final String	RUNSEGMENT_ID								= "_id";
	public static final String	RUNSEGMENT_CHECKPOINT_POINT_ID				= "_idCheckPoint";
	public static final String	RUNSEGMENT_RUN_ID							= "_idRun";
	public static final String	RUNSEGMENT_CHECKPOINT_NO					= "checkPointNo";
	public static final String	RUNSEGMENT_LAP								= "lap";
	public static final String	RUNSEGMENT_TIME								= "segmentTime";
	public static final String	RUNSEGMENT_CREATED_AT						= "createdAt";
	public static final String	RUNSEGMENT_MODIFIED_AT						= "modifiedAt";

	public static final String	ROUTESEGMENT_ID								= "_id";
	public static final String	ROUTESEGMENT_CHECKPOINT_ID					= "_idCheckPoint";
	public static final String	ROUTESEGMENT_ROUTEPOINT_ID					= "_idRoute";
	public static final String	ROUTESEGMENT_CHECKPOINT_NO					= "checkPointNo";
	public static final String	ROUTESEGMENT_DISTANCE						= "distance";
	public static final String	ROUTESEGMENT_CREATED_AT						= "createdAt";
	public static final String	ROUTESEGMENT_MODIFIED_AT					= "modifiedAt";

	// All Results come back in the following results table
	public static final int		RUNRESULTS_RUN_ID_COL						= 0;
	public static final int		RUNRESULTS_NAME_COL							= 1;
	public static final int		RUNRESULTS_ROUTE_ID_COL						= 2;
	public static final int		RUNRESULTS_POINT_NO_COL						= 3;
	public static final int		RUNRESULTS_LONGITUDE_COL					= 4;
	public static final int		RUNRESULTS_LATITUDE_COL						= 5;
	public static final int		RUNRESULTS_TIME_COL							= 6;
	public static final int		RUNRESULTS_LAP_COL							= 7;

	/*
	 * Database Layout
	 * 
	 * jog - Stores each jog entry route - Each jog has a route associated with
	 * it. routePoint - Each route is made from stored points from the journey
	 * time - Times are are associated with points on route during a jog
	 * 
	 */

	// public static final String CREATE_JOG_TABLE = "CREATE TABLE jog (_id
	// INTEGER PRIMARY KEY, name TEXT);";
	//
	// public static final String CREATE_ROUTE_TABLE = "CREATE TABLE route (_id
	// INTEGER PRIMARY KEY, _jogId INTEGER);";
	//
	// public static final String CREATE_ROUTE_POINT_TABLE = "CREATE TABLE
	// routePoint (_id INTEGER PRIMARY KEY, pointNo NUMERIC, _routeId INTEGER,
	// latitude NUMERIC, longitude NUMERIC);";
	//
	// public static final String CREATE_TIME_TABLE = "CREATE TABLE time (_id
	// INTEGER PRIMARY KEY, jogId NUMERIC, routePointId NUMERIC, routeId
	// NUMERIC, lap BOOLEAN, time DATETIME);";
	// END-DB STRUCTURE
	public static String[]		COLUMNS_IN_RUN_ENTRY						= new String[] { RUN_ID, RUN_ROUTE_ID, RUN_NAME, RUN_TOTAL_TIME, RUN_MODIFIED_AT, RUN_CREATED_AT};

	public static String[]		COLUMNS_IN_TIME_ENTRY						= new String[] { RUNSEGMENT_ID, RUNSEGMENT_RUN_ID,
			RUNSEGMENT_CHECKPOINT_POINT_ID, RUNSEGMENT_CHECKPOINT_NO, RUNSEGMENT_TIME, RUNSEGMENT_LAP, RUNSEGMENT_CREATED_AT,
			RUNSEGMENT_MODIFIED_AT											};

	public static String[]		COLUMNS_IN_RUNSEGMENT_ENTRY					= new String[] { ROUTESEGMENT_ID,
			ROUTESEGMENT_CHECKPOINT_ID, ROUTESEGMENT_ROUTEPOINT_ID, ROUTESEGMENT_CHECKPOINT_NO, ROUTESEGMENT_DISTANCE };


	public static final String GET_ALL_RUN_ENTRIES = "SELECT run._id, run._idRoute, runSegment._idCheckPoint, run.name, runSegment.checkPointNo, runSegment.lap, runSegment.SegmentTime, runSegment.createdAt, run.totalTime FROM run, runSegment ";

	public static final String	GET_ALL_JOG_ENTRIES							= "SELECT jog._id, jog.name, jogStartTimes.startTime, jogStopTimes.stopTime "
																					+ "FROM 	jog "
																					+ "INNER JOIN "
																					+ "( "
																					+ "	SELECT DISTINCT startTimes.jogId AS _jogId, startTimes.time AS startTime "
																					+ "	FROM 	time startTimes "
																					+ "	WHERE 	startTimes.time = ( "
																					+ "			SELECT	jog.time "
																					+ "			FROM 	time jog "
																					+ "			WHERE 	jog.jogId = startTimes.jogId "
																					+ "			ORDER BY jog.routePointId ASC "
																					+ "			LIMIT 1 "
																					+ "			) "
																					+ ") jogStartTimes "
																					+ "ON jog._id = jogStartTimes._jogId "
																					+ "INNER JOIN "
																					+ "( "
																					+ "	SELECT DISTINCT stopTimes.jogId AS _jogId, stopTimes.time AS stopTime "
																					+ "	FROM 	time stopTimes "
																					+ "	WHERE 	stopTimes.time = "
																					+ "		( "
																					+ "			SELECT	jog.time "
																					+ "			FROM 	time jog "
																					+ "			WHERE 	jog.jogId = stopTimes.jogId "
																					+ "			ORDER BY jog.routePointId DESC "
																					+ "			LIMIT 1 "
																					+ "		) "
																					+ ") jogStopTimes "
																					+ "ON jog._id = jogStopTimes._jogId";

	public static final String	FETCH_ALL_TODAYS_JOG_ENTRY_START_STOP_TIMES	= "SELECT jog._id, jog.name, jogStartTimes.startTime, jogStopTimes.stopTime "
																					+ "FROM 	jog "
																					+ "INNER JOIN "
																					+ "( "
																					+ "	SELECT DISTINCT startTimes.jogId AS _jogId, startTimes.time AS startTime "
																					+ "	FROM 	time startTimes "
																					+ "	WHERE 	startTimes.time = ( "
																					+ "			SELECT	jog.time "
																					+ "			FROM 	time jog "
																					+ "			WHERE 	jog.jogId = startTimes.jogId "
																					+ "			AND	strftime(\"%Y-%m-%d\", jog.time) = strftime(\"%Y-%m-%d\", 'now') "
																					+ "			ORDER BY jog.routePointId ASC "
																					+ "			LIMIT 1 "
																					+ "			) "
																					+ ") jogStartTimes "
																					+ "ON jog._id = jogStartTimes._jogId "
																					+ "INNER JOIN "
																					+ "( "
																					+ "	SELECT DISTINCT stopTimes.jogId AS _jogId, stopTimes.time AS stopTime "
																					+ "	FROM 	time stopTimes "
																					+ "	WHERE 	stopTimes.time = "
																					+ "		( "
																					+ "			SELECT	jog.time "
																					+ "			FROM 	time jog "
																					+ "			WHERE 	jog.jogId = stopTimes.jogId "
																					+ "			AND	strftime(\"%Y-%m-%d\", jog.time) = strftime(\"%Y-%m-%d\", 'now') "
																					+ "			ORDER BY jog.routePointId DESC "
																					+ "			LIMIT 1 "
																					+ "		) "
																					+ ") jogStopTimes "
																					+ "ON jog._id = jogStopTimes._jogId";

	public static String		FETCH_ALL_TODAYS_JOG_ENTRIES				= "SELECT DISTINCT jogdetails._jogId, jogdetails.name, route._id, routeandpoints.pointNo, routeandpoints.longitude, routeandpoints.latitude, timesroutesandpoints.time, timesroutesandpoints.lap "
																					+ "FROM route "
																					+ "INNER JOIN ("
																					+ "			SELECT routePoint._routeId AS _routeId, routePoint._id AS pointId, routePoint.pointNo AS pointNo, routePoint.latitude AS latitude, routePoint.longitude AS longitude "
																					+ "			FROM routePoint, route "
																					+ "			WHERE route._id = routePoint._routeId "
																					+ "		) routeandpoints "
																					+ "ON route._id = routeandpoints._routeId "
																					+ "INNER JOIN ("
																					+ "			SELECT time.lap AS lap, time.time AS time, time.routePointId AS routePointId "
																					+ "			FROM time, route "
																					+ "			WHERE route._id = time.routeId "
																					+ "			AND	strftime( \"%Y-%m-%d\", time.time ) = strftime( \"%Y-%m-%d\",\'now') "
																					+ "		) timesroutesandpoints "
																					+ "ON routeandpoints.pointId = timesroutesandpoints.routePointId "
																					+ "INNER JOIN ( "
																					+ "			SELECT jog._id AS _jogId, jog.name AS name "
																					+ "			FROM jog "
																					+ "		) jogdetails "
																					+ "ON route._jogId = jogdetails._jogId";

	public static String selectAJogEntry(String jogId) {
		return "  SELECT DISTINCT jogdetails._jogId, jogdetails.name, route._id, routeandpoints.pointNo, routeandpoints.longitude, routeandpoints.latitude, timesroutesandpoints.time, timesroutesandpoints.lap "
				+ "FROM route "
				+ "INNER JOIN ("
				+ "			SELECT routePoint._routeId AS _routeId, routePoint._id AS pointId, routePoint.pointNo AS pointNo, routePoint.latitude AS latitude, routePoint.longitude AS longitude "
				+ "			FROM routePoint, route"
				+ "			WHERE route._id = routePoint._routeId "
				+ "			AND route._jogId = "
				+ jogId
				+ "		) routeandpoints "
				+ "ON route._id = routeandpoints._routeId "
				+ "INNER JOIN ("
				+ "			SELECT time.lap AS lap, time.time AS time, time.routePointId AS routePointId "
				+ "			FROM time, route "
				+ "			WHERE route._id = time.routeId "
				+ "			AND route._jogId = "
				+ jogId
				+ "		) timesroutesandpoints "
				+ "ON routeandpoints.pointId = timesroutesandpoints.routePointId "
				+ "INNER JOIN ("
				+ "			SELECT jog._id AS _jogId, jog.name AS name "
				+ "			FROM jog"
				+ "			WHERE jog._id = "
				+ jogId
				+ "		) jogdetails " + "ON route._jogId = jogdetails._jogId";
	}

	public static String	FETCH_STUBBEDOUT_JOG_ENTRIES	= "SELECT DISTINCT jogdetails._jogId, jogdetails.name, route._id, routeandpoints.pointNo, routeandpoints.longitude, routeandpoints.latitude, timesroutesandpoints.time, timesroutesandpoints.lap"
																	+ "FROM route "
																	+ "INNER JOIN ("
																	+ "			SELECT routePoint._routeId AS _routeId, routePoint._id AS pointId, routePoint.pointNo AS pointNo, routePoint.latitude AS latitude, routePoint.longitude AS longitude"
																	+ "			FROM routePoint, route"
																	+ "			WHERE route._id = routePoint._routeId "
																	+ "		) routeandpoints "
																	+ "ON route._id = routeandpoints._routeId "
																	+ "INNER JOIN ("
																	+ "			SELECT time.lap AS lap, time.time AS time, time.routePointId AS routePointId"
																	+ "			FROM time, route"
																	+ "			WHERE route._id = time.routeId "
																	+ "			AND	strftime( \"%Y-%m-%d\", time.time ) = strftime( \"%Y-%m-%d\",\'2008-03-10T10:00:00:00.000') "
																	+ "		) timesroutesandpoints "
																	+ "ON routeandpoints.pointId = timesroutesandpoints.routePointId "
																	+ "INNER JOIN ("
																	+ "			SELECT jog._id AS _jogId, jog.name AS name"
																	+ "			FROM jog"
																	+ "		) jogdetails "
																	+ "ON route._jogId = jogdetails._jogId";

	public static String getJogId(String jogName) {
		return "SELECT _id " + "FROM jog " + "WHERE name = " + jogName + "LIMIT 1";
	}

	public static String insertJogName(String jogName) {
		return "INSERT INTO jog (name) VALUES ( \'" + jogName + "\')";
	}

	public static String selectJustEnteredJogId() {
		return "SELECT _id " + "FROM jog " + "ORDER BY _id DESC " + "LIMIT 1";
	}

	public static String addATimeEntry(String jogId, String routeId, String routePointId, String time, String lap) {
		return "INSERT INTO time (jogId, routePointId, routeId, time, lap) " + "VALUES (\'" + jogId + "\',\'" + routePointId
				+ "\',\'" + routeId + "\',\'" + time + "\',\'" + lap + "\');";
	}

	public static final String	WHERE_ENTRIES_WERE_TODAY		= "strftime( \"%Y-%m-%d\"," + RUNSEGMENT_TIME
																		+ ") = strftime( \"%Y-%m-%d\",'now');";

	public static final String	WHERE_ENTRIES_WERE_THIS_MONTH	= "strftime( \"%Y-%m\"," + RUNSEGMENT_TIME
																		+ ") = strftime( \"%Y-%m\",'now');";

	public static String whereEntriesWereThisMonth(Calendar calendar) {
		String query = "strftime( \"%Y-%m\"," + RUNSEGMENT_TIME + ") = strftime( \"%Y-%m\",'"
				+ DBHelperTools.isoFormat(calendar.getTimeInMillis()) + "');";

		return query;
	}

	// Entries must be entered into DB in YYYY-MM-DDTHH:MM:SS.SSS format
	public static String getWhereCondition(Integer day, Integer month, Integer year) {
		String dayString;
		String monthString;

		if (day < 10) {
			dayString = "0" + day.toString();
		} else {
			dayString = day.toString();
		}

		if (month < 10) {
			monthString = "0" + month.toString();
		} else {
			monthString = month.toString();
		}

		return "strftime( \"%Y-%m-%d\"," + RUNSEGMENT_TIME + ") = strftime( \"%Y-%m-%d\",\"" + year.toString() + "-" + monthString
				+ "-" + dayString + "T12:00:00.000\");";
	}

	public static String GET_ENTRIES(Integer day, Integer month, Integer year) {

		String dayString;
		String monthString;

		if (day < 10) {
			dayString = "0" + day.toString();
		} else {
			dayString = day.toString();
		}

		if (month < 10) {
			monthString = "0" + month.toString();
		} else {
			monthString = month.toString();
		}

		String query = "SELECT DISTINCT jogdetails._jogId, jogdetails.name, route._id, routeandpoints.pointNo, routeandpoints.longitude, routeandpoints.latitude, timesroutesandpoints.time, timesroutesandpoints.lap "
				+ "FROM route "
				+ "INNER JOIN ("
				+ "			SELECT routePoint._routeId AS _routeId, routePoint._id AS pointId, routePoint.pointNo AS pointNo, routePoint.latitude AS latitude, routePoint.longitude AS longitude "
				+ "			FROM routePoint, route "
				+ "			WHERE route._id = routePoint._routeId "
				+ "		) routeandpoints "
				+ "ON route._id = routeandpoints._routeId "
				+ "INNER JOIN ("
				+ "			SELECT time.lap AS lap, time.time AS time, time.routePointId AS routePointId "
				+ "			FROM time, route "
				+ "			WHERE route._id = time.routeId "
				+ "			AND	strftime( \"%Y-%m-%d\","
				+ RUNSEGMENT_TIME
				+ ") = strftime( \"%Y-%m-%d\",\'"
				+ year.toString()
				+ "-"
				+ monthString
				+ "-"
				+ dayString
				+ "T12:00:00.000\') "
				+ "		) timesroutesandpoints "
				+ "ON routeandpoints.pointId = timesroutesandpoints.routePointId "
				+ "INNER JOIN ( "
				+ "			SELECT jog._id AS _jogId, jog.name AS name "
				+ "			FROM jog "
				+ "		) jogdetails "
				+ "ON route._jogId = jogdetails._jogId";

		return query;

	}

	public static String GET_MONTH_ENTRIES(Integer month, Integer year) {

		String monthString;

		if (month < 10) {
			monthString = "0" + month.toString();
		} else {
			monthString = month.toString();
		}

		String query = "SELECT DISTINCT jogdetails._jogId, jogdetails.name, route._id, routeandpoints.pointNo, routeandpoints.longitude, routeandpoints.latitude, timesroutesandpoints.time, timesroutesandpoints.lap "
				+ "FROM route "
				+ "INNER JOIN ("
				+ "			SELECT routePoint._routeId AS _routeId, routePoint._id AS pointId, routePoint.pointNo AS pointNo, routePoint.latitude AS latitude, routePoint.longitude AS longitude "
				+ "			FROM routePoint, route "
				+ "			WHERE route._id = routePoint._routeId "
				+ "		) routeandpoints "
				+ "ON route._id = routeandpoints._routeId "
				+ "INNER JOIN ("
				+ "			SELECT time.lap AS lap, time.time AS time, time.routePointId AS routePointId "
				+ "			FROM time, route "
				+ "			WHERE route._id = time.routeId "
				+ "			AND	strftime( \"%Y-%m\","
				+ RUNSEGMENT_TIME
				+ ") = strftime( \"%Y-%m\",\'"
				+ year.toString()
				+ "-"
				+ monthString
				+ "-01T12:00:00.000\') "
				+ "		) timesroutesandpoints "
				+ "ON routeandpoints.pointId = timesroutesandpoints.routePointId "
				+ "INNER JOIN ( "
				+ "			SELECT jog._id AS _jogId, jog.name AS name "
				+ "			FROM jog "
				+ "		) jogdetails "
				+ "ON route._jogId = jogdetails._jogId";

		return query;

	}
	
//	SELECT run._id, run._idRoute, runSegment._idCheckPoint, run.name, runSegment.checkPointNo, runSegment.lap, runSegment.SegmentTime, runSegment.createdAt, run.totalTime 
//	FROM run, runSegment
//	WHERE strftime('%Y-%m-%d', runSegment.createdAt) = strftime('%Y-%m-%d','2008-03-10T02:34:56'); 

	public static String GET_RUN_ENTRIES_FOR_ISO_DATE(String date) {
		return GET_ALL_RUN_ENTRIES + " WHERE strftime( \'%Y-%m-%d\', runSegment.createdAt) = strftime(\'%Y-%m-%d\', '"+ date + "');";
	}
	
	public static String GET_RUN_ENTRIES_AFTER_ISO_DATE(String date) {
		return GET_ALL_RUN_ENTRIES + " WHERE strftime( \'%Y-%m-%d\', runSegment.createdAt) = strftime(\'%Y-%m-%d\', '"+ date + "');";
	}
	
	public static String GET_RUN_ENTRIES_BEFORE_ISO_DATE(String date) {
		return GET_ALL_RUN_ENTRIES + " WHERE strftime( \'%Y-%m-%d\', runSegment.createdAt) = strftime(\'%Y-%m-%d\', '"+ date + "');";
	}
	
	public static String GET_RUN_ENTRIES_BETWEEN_ISO_DATES(String from, String to) {
		return GET_ALL_RUN_ENTRIES 
		+ " WHERE strftime(\'%Y-%m-%d\', createdAt) >= strftime(\'%Y-%m-%d\', '"+ from + "') "
		+ "AND strftime(\'%Y-%m-%d\', createdAt) <= strftime(\'%Y-%m-%d\', '"+ to + "');";
	}

}
